package com.apes.fn.server.servicePlan.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.fn.server.servicePlan.service.ServicePlanFactory;
import com.apes.framework.api.SpringManager;
import com.apes.framework.query.ApesDao;
import com.apes.scm.masterdata.frame.model.Dept;
import com.apes.scm.masterdata.frame.repository.DeptRepository;
import com.apes.scm.masterdata.stock.model.Location;
import com.apes.scm.masterdata.stock.service.LocationService;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.util.Date;

@Component
public class ServicePlanDao extends ApesDao {

    @Value("${apes.switched:false}")
    private Boolean switched;
    /***
     * 拼接查询的SQL 并返回分页的数据
     * @param param
     * @return
     */
    public JSONArray queryEmployeeByBranchId(JSONObject param) {
        String sSQL = "SELECT G.* " +
                "  FROM (SELECT G.*, ROWNUM RN, CEIL(TOTAL / :SIZE) PAGE " +
                "           FROM (" +
                " SELECT MAX(ROWNUM) OVER() TOTAL, B.ID,C.NAME NAME, D.NAME POST_NAME " +
                " FROM HR_EMPLOYEE A, MAS_PARTY_ROLE B, MAS_PARTY C, HR_WORK_POST D WHERE A.PARTY_ID = B.PARTY_ID AND D.ID=A.POST_ID AND B.PARTY_ID = C.ID AND B.ROLE_ID = 'Employee' "
                + param.getString("condition")//拼接条件
                +" AND A.ID IN (SELECT DISTINCT E_ID FROM ( " +
                " SELECT A.TRANSFER_UID E_ID FROM HR_TRANSFER_APPLICATION_ITEM A,HR_EMPLOYEE B WHERE A.EFFECTIVE_DATE <= SYSDATE " +
                " AND A.END_DATE >= SYSDATE AND A.REPEAL_DATE IS NULL AND A.BRANCH_ID_AFTER = :branchId AND B.EMPLOYEE_STATE_ID = '000088' AND A.TRANSFER_UID = B.ID" +
                " UNION ALL " +
                " SELECT A.ID E_ID FROM HR_EMPLOYEE A WHERE A.WORK_BRANCH_ID IS NOT NULL AND A.STATE <> 'delete' " +
                "  AND A.EMPLOYEE_STATE_ID = '000088' AND A.WORK_BRANCH_ID = :branchId " +
                " )) " +
                ") G " +
                "          WHERE ROWNUM <= DECODE(0, :PAGE, :SIZE, (:PAGE + 1) * :SIZE)) G " +
                " WHERE RN > (:PAGE) * :SIZE";

        return toJSONArray(find(sSQL, param));
    }


    public Double queryLocalStore(JSONObject param){
        String sSQL = " SELECT (C.USABLE_QTY +C.TRANSFER_RECEIVE_QTY + C.PURCHASING_WAY_QTY +  " +
                "       C.TRANSFER_IN_BILLING_QTY + C.REQUIRE_BILLING_QTY + " +
                "        C.SALES_CONTROL_QTY - C.FROZEN_QTY) STOCK_AMOUNT "
                    + " FROM ACC_STOCK C, MAS_LOCATION D, MAS_DEPOT E "
                    + " WHERE C.LOCATION_ID = D.ID AND D.USAGE_ID = '1' AND D.DEPOT_ID = E.ID AND E.DEPT_ID = :deptId AND C.PRODUCT_ID = :productId AND D.VALID = 1 ";
        JSONArray array = toJSONArray(find(sSQL, param));
        if(array.size() == 0) return 0.0;
        return array.getJSONObject(0).getDouble("STOCK_AMOUNT");
    }


    public JSONArray queryForReceive(JSONObject param) {
        String sSQL = "SELECT A.ID, A.CREATOR_ID, A.CONFIRM_UID, A.BELONG_DEPT_ID, A.STATE, " +
                "       TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') CREATE_DATE, " +
                "       TO_CHAR(A.CONFIRM_DATE, 'YYYY-MM-DD HH24:MI:SS') CONFIRM_DATE, " +
                "       (SELECT B.NAME FROM MAS_DEPT B WHERE A.BELONG_DEPT_ID = B.ID) BELONG_DEPT_NAME, " +
                "       (SELECT B.CAR_LICENSE FROM FN_CUSTOMER_EXPAND B WHERE A.FN_CUSTOMER_EXPAND_ID =B.ID) CAR_LICENSE, " +
                "       (SELECT C.NAME " +
                "           FROM MAS_PARTY_ROLE B, MAS_PARTY C " +
                "          WHERE A.CREATOR_ID = B.ID " +
                "            AND B.PARTY_ID = C.ID) CREATOR_NAME, " +
                "       (SELECT C.NAME " +
                "           FROM MAS_PARTY_ROLE B, MAS_PARTY C " +
                "          WHERE A.CONFIRM_UID = B.ID " +
                "            AND B.PARTY_ID = C.ID) CONFIRM_NAME " +
                "  FROM SER_SERVICE_PLAN A " +
                " WHERE A.STATE = 'confirm' " +
                "   AND A.SERVICE_DEPT_ID = :DEPT " +
                "   AND A.BELONG_DEPT_ID = :DEPT " +
                "   AND EXISTS (SELECT 1 " +
                "          FROM SER_SERVICE_PLAN_ITEM B, MAS_PRODUCT C " +
                "         WHERE A.ID = B.SERVICE_PLAN_ID " +
                "           AND B.SERVICE_STATE = 1 " +
                "           AND B.SALE_QTY - B.QTY_RECEIVE > 0 " +
                "           AND B.PRODUCT_ID = C.ID " +
                "           AND C.PRODUCT_GROUP_ID = '10' )";
        if( param.get("ID") != null ) sSQL += "AND A.ID = '" + param.getString("ID") + "'";
        if( param.get("CAR_LICENSE") != null ) sSQL += "AND EXISTS ( SELECT 1 FROM FN_CUSTOMER_EXPAND B WHERE A.FN_CUSTOMER_EXPAND_ID =B.ID AND B.CAR_LICENSE LIKE '%" + param.getString("CAR_LICENSE") + "%')";

        return toJSONArray(find(sSQL, param));
    }

    public JSONArray queryForInquiry(JSONObject param) {
        String sSQL = "SELECT * " +
                "  FROM (SELECT * " +
                "  FROM (SELECT DISTINCT A.ID, A.INNER_BUSINESS_TYPE, A.CREATOR_ID, A.BELONG_DEPT_ID DEPT_ID, " +
                "                DECODE(A.STATE, 'create', '新增', 'toTechAudit', '转技术审核', " +
                "                        'techAudit', '技术审核完成', 'backTechAudit', '技术审核退回', " +
                "                        'toPriceAudit', '转价格审核', 'priceAudit', '价格审核', " +
                "                        'backPriceAudit', '价格审核退回', 'toInquiry', '转询价', " +
                "                        'inquiryFinish', '询价完成', 'backInquiry', '询价退回', " +
                "                        'submission', '提交', 'goBack', '退回', 'confirm', '确认', " +
                "                        'qualityTest', '质检', 'termination', '终止', 'receivables', " +
                "                        '收款完成', '无') STATE, " +
                "                TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') CREATE_DATE, " +
                "                '服务方案' INNER_BUSINESS_TYPE_NAME, " +
                "                (SELECT B.NAME FROM MAS_DEPT B WHERE A.BELONG_DEPT_ID = B.ID) DEPT_NAME, " +
                "                (SELECT B.CAR_LICENSE " +
                "                    FROM FN_CUSTOMER_EXPAND B " +
                "                   WHERE A.FN_CUSTOMER_EXPAND_ID = B.ID) CAR_LICENSE, " +
                "                (SELECT C.NAME " +
                "                    FROM MAS_PARTY_ROLE B, MAS_PARTY C " +
                "                   WHERE A.CREATOR_ID = B.ID " +
                "                     AND B.PARTY_ID = C.ID) CREATOR_NAME " +
                "           FROM SER_SERVICE_PLAN A LEFT JOIN SER_SERVICE_PLAN_ITEM  B ON A.ID = B.SERVICE_PLAN_ID " +
                "          WHERE A.STATE NOT IN " +
                "                ('toInquiry', 'termination', 'toTechAudit', 'toPriceAudit', " +
                "                 'backTechAudit', 'qualityTest', 'receivables', 'confirm') " +
                "            AND A.BELONG_DEPT_ID = :DEPT " +
                "            AND A.LOCK_SERVICE_PLAN = 0 " +
                "            AND A.BELONG_DEPT_ID = A.SERVICE_DEPT_ID " +
                "            AND A.TERMINATION_DATE IS NULL " +
                "                    AND B.SERVICE_STATE = 1 " +
                "                    AND B.TERMINATION_FLAG = 0 " +
                "                    AND (B.FITTING <> '009525' OR " +
                "                        (B.FITTING = '009525' AND B.PRODUCT_RULE = 1 AND " +
                "                        A.STATE <> 'confirm') OR " +
                "                        (B.FITTING IS NULL AND " +
                "                        ((SELECT C.CATEGORY_ID " +
                "                              FROM MAS_PRODUCT C " +
                "                             WHERE B.PRODUCT_ID = C.ID) <> '009525' OR " +
                "                        ((SELECT C.CATEGORY_ID " +
                "                               FROM MAS_PRODUCT C " +
                "                              WHERE B.PRODUCT_ID = C.ID) = '009525' AND " +
                "                        B.PRODUCT_RULE = 1 AND A.STATE <> 'confirm')))) ) G " +
                "   UNION ALL " +
                "   (SELECT ID,INNER_BUSINESS_TYPE, CREATOR_ID, " +
                "           FIXED_LOSS_DEPT DEPT_ID, " +
                "           DECODE(A.STATE, 'create', '新增', 'toTechAudit', '转技术审核', " +
                "                          'techAudit', '技术审核完成', 'backTechAudit', '技术审核退回', " +
                "                          'toPriceAudit', '转价格审核', 'priceAudit', '价格审核', " +
                "                          'backPriceAudit', '价格审核退回', 'toInquiry', '转询价', " +
                "                          'inquiryFinish', '询价完成', 'backInquiry', '询价退回', " +
                "                          'submission', '提交', 'goBack', '退回', 'confirm', '确认', " +
                "                          'qualityTest', '质检', 'termination', '终止', 'receivables', " +
                "                          '收款完成', '无')                                           STATE, " +
                "           TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS')               CREATE_DATE, " +
                "           '定损报价'               INNER_BUSINESS_TYPE_NAME, " +
                "           (SELECT B.NAME FROM MAS_DEPT B WHERE A.FIXED_LOSS_DEPT = B.ID) DEPT_NAME, " +
                "           (SELECT B.CAR_LICENSE FROM FN_CUSTOMER_EXPAND B WHERE A.CUSTOMER_EXPAND_ID = B.ID) CAR_LICENSE, " +
                "           (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C WHERE A.CREATOR_ID = B.ID AND B.PARTY_ID = C.ID) CREATOR_NAME " +
                "   FROM SER_LOSS_QUOTATION A WHERE A.FIXED_LOSS_DEPT = :DEPT)) C " +
                "    WHERE 1 = 1 AND C.STATE <> '无' ";
        if( param.get("ID") != null ) sSQL += "AND C.ID = '" + param.getString("ID") + "'";
        if( param.get("CAR_LICENSE") != null ) sSQL += "AND C.CAR_LICENSE = '" + param.getString("CAR_LICENSE") + "'";
        sSQL += " ORDER BY C.CREATE_DATE DESC ";
        return toJSONArray(find(sSQL, param));
    }



    /***
     * 拼接查询的SQL 并返回分页的数据
     * @param param
     * @return
     */
    public JSONArray queryProduct(JSONObject param) {
        if (!param.containsKey("STAR_PERSON")) param.put("STAR_PERSON","0");
        String sSQL = "SELECT G.* " +
                "  FROM (SELECT G.*, ROWNUM RN, CEIL(TOTAL / :SIZE) PAGE " +
                "           FROM (SELECT MAX(ROWNUM) OVER() TOTAL,a. ID, STOCK_AMOUNT, SUB_STOCK,GENERAL_STOCK,USABLE_QTY,FROZEN_QTY,TRANSFER_RECEIVE_QTY,PURCHASING_WAY_QTY,TRANSFER_IN_BILLING_QTY,REQUIRE_BILLING_QTY,nvl(b.VALID,0) star_flag," +
                "   (select C.CLASSIFY_ID from mas_product C where A.ID = C.ID)CLASSIFY_ID  " +
                "                    FROM ("+ queryProductCore(param) +")A " +
                "   left join star_product B on b.PRODUCT_ID = A.ID and b.STAR_PERSON = :STAR_PERSON " +
                "                   WHERE 1 = 1  " ;
        if (param.get("queryStock") != null && param.getBoolean("queryStock") ) {
            sSQL += " AND ((STOCK_AMOUNT + SUB_STOCK + GENERAL_STOCK > 0) or exists (select 1 from mas_product_negative_selling pns where pns.PRODUCT_ID = A.ID)) " ;
        } else  {
            sSQL += "   AND ( STATE <> 2 " +
                    "   OR (STATE = 2 AND " +
                    "    STOCK_AMOUNT + SUB_STOCK + GENERAL_STOCK > 0))  " ;
        }
        // 不匹配模式：频闭配件名称为待编译的商品
        if (param.getString("relationType").equals("yes")) {
            sSQL += " AND (CATEGORY_ID <> '013951' or  (STOCK_AMOUNT + SUB_STOCK + GENERAL_STOCK > 0 ))";
        }

        if(param.get("queryStock") != null && param.getBoolean("queryStock") ){
            sSQL += " AND (STOCK_AMOUNT + SUB_STOCK + GENERAL_STOCK > 0)";
        }
        if (param.get("stockValid") != null) {
            sSQL += " and (STOCK_AMOUNT + SUB_STOCK + GENERAL_STOCK <= 0) ";
        }
        sSQL += "ORDER BY STOCK_AMOUNT DESC, SUB_STOCK DESC, GENERAL_STOCK DESC) G " +
                "          WHERE ROWNUM <= DECODE(0, :PAGE, :SIZE, (:PAGE + 1) * :SIZE)) G " +
                " WHERE RN > (:PAGE) * :SIZE";
        return toJSONArray(find(sSQL, param));
    }

    /***
     * 拼接查询的SQL 只查询一个商品
     * @param param
     * @return
     */
    public JSONArray queryOneProduct(JSONObject param) {
        String sSQL = "SELECT MAX(ROWNUM) OVER() TOTAL, ID, SUB_STOCK,GENERAL_STOCK,USABLE_QTY,FROZEN_QTY,TRANSFER_RECEIVE_QTY,PURCHASING_WAY_QTY,TRANSFER_IN_BILLING_QTY,REQUIRE_BILLING_QTY " +
                "                    FROM ("+ queryProductCore(param) +") " +
                "                   WHERE 1 = 1" ;
            sSQL += " AND STOCK_AMOUNT + SUB_STOCK + GENERAL_STOCK > 0 " ;
        sSQL += "ORDER BY STOCK_AMOUNT DESC, SUB_STOCK DESC, GENERAL_STOCK DESC";
        return toJSONArray(find(sSQL, param));
    }

    //mas_dc_distribution_range

    /**
     * 查询DC配送范围
     * @param param
     * @return
     */
    public JSONArray queryProductForDC(JSONObject param){
        String sSQL = "SELECT L.*,(SELECT P.NAME FROM MAS_PARTY P WHERE P.ID =(SELECT C.party_id FROM MAS_PARTY_ROLE C WHERE C.ID = L.COMPANY_ID))COMPANY_NAME,(SELECT E.DEPOT_LEVEL FROM MAS_DEPOT E WHERE E.ID = L.DEPOT_ID) depot_level_id FROM MAS_LOCATION L WHERE L.ID IN (SELECT D.F_LOCATION_ID FROM MAS_DC_DISTRIBUTION_RANGE D " +
                " WHERE D.C_LOCATION_ID IN ('"+ param.getString("locationId")+"')) AND L.depot_id IS NOT NULL";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询DC配送范围
     * @param param
     * @return
     */
    public JSONArray queryProductStock(JSONObject param){
        String sSQL = "SELECT * FROM MAS_DC_DISTRIBUTION_RANGE D " +
                "WHERE C_location_id IN ("+ param.getString("location"+")");
        return toJSONArray(find(sSQL, param));
    }


    // 核心sSQL
    private String queryProductCore(JSONObject param) {
        String sSQL = "";

        sSQL += "SELECT A.ID, A.STATE, A.BRAND_ID, A.CLASSIFY_ID, A.CATEGORY_ID,  " +
                "       NVL((SELECT (C.USABLE_QTY + C.TRANSFER_RECEIVE_QTY + C.PURCHASING_WAY_QTY + " +
                "       C.TRANSFER_IN_BILLING_QTY + C.REQUIRE_BILLING_QTY + " +
                "        C.SALES_CONTROL_QTY - C.FROZEN_QTY)  STOCK_AMOUNT " +
                "           FROM ACC_STOCK C, MAS_LOCATION D, MAS_DEPOT E " +
                "          WHERE C.LOCATION_ID = D.ID " +
                "            AND D.USAGE_ID = '1' " +
                "            AND D.DEPOT_ID = E.ID " +
                "            AND E.DEPT_ID = :NEW_DEPT_ID " +
                "            AND C.PRODUCT_ID = A.ID AND D.VALID = 1), 0) STOCK_AMOUNT, "+
                "            NVL((SELECT c.USABLE_QTY  "+
                "              FROM ACC_STOCK C, MAS_LOCATION D, MAS_DEPOT E "+
                "            WHERE C.LOCATION_ID = D.ID "+
                "              AND D.USAGE_ID = '1' "+
                "              AND D.DEPOT_ID = E.ID "+
                "              AND E.DEPT_ID = :NEW_DEPT_ID "+
                "              AND C.PRODUCT_ID = A.ID "+
                "              AND D.VALID = 1), "+
                "           0) USABLE_QTY, "+
                "            NVL((SELECT c.FROZEN_QTY  "+
                "              FROM ACC_STOCK C, MAS_LOCATION D, MAS_DEPOT E "+
                "            WHERE C.LOCATION_ID = D.ID "+
                "              AND D.USAGE_ID = '1' "+
                "              AND D.DEPOT_ID = E.ID "+
                "              AND E.DEPT_ID = :NEW_DEPT_ID "+
                "              AND C.PRODUCT_ID = A.ID "+
                "              AND D.VALID = 1), "+
                "           0) FROZEN_QTY, "+
                "           NVL((SELECT c.TRANSFER_RECEIVE_QTY "+
                "             FROM ACC_STOCK C, MAS_LOCATION D, MAS_DEPOT E "+
                "            WHERE C.LOCATION_ID = D.ID "+
                "              AND D.USAGE_ID = '1' "+
                "              AND D.DEPOT_ID = E.ID "+
                "              AND E.DEPT_ID = :NEW_DEPT_ID "+
                "              AND C.PRODUCT_ID = A.ID "+
                "              AND D.VALID = 1), "+
                "           0) TRANSFER_RECEIVE_QTY, "+
                "           NVL((SELECT c.PURCHASING_WAY_QTY "+
                "             FROM ACC_STOCK C, MAS_LOCATION D, MAS_DEPOT E "+
                "            WHERE C.LOCATION_ID = D.ID "+
                "              AND D.USAGE_ID = '1' "+
                "              AND D.DEPOT_ID = E.ID "+
                "              AND E.DEPT_ID = :NEW_DEPT_ID "+
                "              AND C.PRODUCT_ID = A.ID "+
                "              AND D.VALID = 1), "+
                "           0) PURCHASING_WAY_QTY, "+
                "           NVL((SELECT c.TRANSFER_IN_BILLING_QTY "+
                "             FROM ACC_STOCK C, MAS_LOCATION D, MAS_DEPOT E "+
                "            WHERE C.LOCATION_ID = D.ID "+
                "              AND D.USAGE_ID = '1' "+
                "              AND D.DEPOT_ID = E.ID "+
                "              AND E.DEPT_ID = :NEW_DEPT_ID "+
                "              AND C.PRODUCT_ID = A.ID "+
                "              AND D.VALID = 1), "+
                "           0) TRANSFER_IN_BILLING_QTY, "+
                "           NVL((SELECT c.REQUIRE_BILLING_QTY "+
                "             FROM ACC_STOCK C, MAS_LOCATION D, MAS_DEPOT E "+
                "            WHERE C.LOCATION_ID = D.ID "+
                "              AND D.USAGE_ID = '1' "+
                "              AND D.DEPOT_ID = E.ID "+
                "              AND E.DEPT_ID = :NEW_DEPT_ID "+
                "              AND C.PRODUCT_ID = A.ID "+
                "              AND D.VALID = 1), "+
                "           0) REQUIRE_BILLING_QTY, ";

            sSQL += "" +
                    "         NVL (" +
                    "     (SELECT SUM(T.USABLE_QTY)  " +
                    "        FROM ACC_STOCK T " +
                    "       WHERE T.LOCATION_ID in :areaLocation  " +
                    "       AND T.PRODUCT_ID = A.ID " +
                    "       AND T.USABLE_QTY >= 0 ), 0) SUB_STOCK, " +
                    " NVL((SELECT SUM(T.USABLE_QTY)  " +
                    "  FROM ACC_STOCK T " +
                    " WHERE T.LOCATION_ID in :totalLocation  " +
                    "   AND T.PRODUCT_ID = A.ID  ), 0 ) GENERAL_STOCK " +
                    "  FROM MAS_PRODUCT A, MAS_CATEGORY B, MAS_BRAND C " +
                    " WHERE A.CATEGORY_ID = B.ID AND A.BRAND_ID = C.ID " +
                    " AND A.STATE <> '3'";


        //分销单需要增加50商品组
        if (param.get("PRODUCT_GROUP") != null) {
            sSQL +=  " AND A.PRODUCT_GROUP_ID IN ('10','20', '50') ";
        } else {
            sSQL +=  " AND A.PRODUCT_GROUP_ID IN ('10','20') " ;
        }


        // 界面传入条件
        if(param.get("PRODUCT_TEMP") != null){
            sSQL += "AND ( A.ID = :PRODUCT_TEMP OR A.NAME LIKE '%"+ regexSplit(param.getString("PRODUCT_TEMP")) + "%' OR B.ID = :PRODUCT_TEMP OR B.NAME LIKE '%"+ param.getString("PRODUCT_TEMP") + "%' )";
        }

        if(param.get("SPECIFITATION_TEMP") != null){
            sSQL += "AND ( A.SPECIFICATIONS LIKE '%"+ param.getString("SPECIFITATION_TEMP") + "%' OR A.MODEL LIKE '%"+ param.getString("SPECIFITATION_TEMP") + "%' )";
        }

        // 控制走自动或者匹配模式的商品不经过配件名称的筛选
        // 存在匹配之后的配件名称和实际查询名称不一致的情况
        if(param.get("CATEGORY_TEMP") != null && param.getString("relationType").equals("yes")) {
            sSQL += "AND ( B.ID = :CATEGORY_TEMP OR B.NAME LIKE '%"+ param.getString("CATEGORY_TEMP") + "%')";
        }

        //增加品质查询条件
        if(param.get("quality") != null ) {
            sSQL += "AND ( C.QUALITY IN (" + param.getString("quality") +"))";
        }

        // 老框架配置之后的商品数组
        if(param.get("IDS") != null){
            sSQL += "AND A.ID IN (" + param.getString("IDS") +")";
        }

        // 服务购物车查询商品带过来的条件
        if (param.get("CUSTOM_MARKER") != null && !StringUtils.isEmpty(param.getString("CUSTOM_MARKER"))) {
            sSQL += " AND A.CUSTOM_MARKER = 1 AND A.BRAND_ID = '003676' ";
        }
        //分销单的条件
        if (param.get("PRODUCT_CLASSIFY") != null && !StringUtils.isEmpty(param.getString("PRODUCT_CLASSIFY"))) {
            sSQL += "  AND A.CLASSIFY_ID =:PRODUCT_CLASSIFY ";
        }
        if (param.get("PRODUCT_BRAND") != null && !StringUtils.isEmpty(param.getString("PRODUCT_BRAND"))) {
            sSQL += " AND A.BRAND_ID =:PRODUCT_BRAND ";
        }


//        sSQL += " AND ROWNUM < 1000";
        return sSQL;
    }

    /**
     * 根据商品编码去查，上限100条
     * @param param
     * @return
     */
    public JSONArray queryProductStoreForList(JSONObject param){
        String sSQL = "";
        if(!switched) {
            sSQL += "SELECT A.ID,A.NAME, A.STATE, " +
                    "       NVL((SELECT (C.USABLE_QTY +C.TRANSFER_RECEIVE_QTY + C.PURCHASING_WAY_QTY + " +
                    "       C.TRANSFER_IN_BILLING_QTY + C.REQUIRE_BILLING_QTY + " +
                    "        C.SALES_CONTROL_QTY - C.FROZEN_QTY)  STOCK_AMOUNT " +
                    "           FROM ACC_STOCK C, MAS_LOCATION D, MAS_DEPOT E " +
                    "          WHERE C.LOCATION_ID = D.ID " +
                    "            AND D.USAGE_ID = '1' " +
                    "            AND D.DEPOT_ID = E.ID " +
                    "            AND E.DEPT_ID = :NEW_DEPT_ID " +
                    "            AND C.PRODUCT_ID = A.ID AND D.VALID = 1), 0) STOCK_AMOUNT, " +
                    "       NVL((SELECT KYS " +
                    "           FROM VIEW_QYCKYS C " +
                    "          WHERE C.SPID = A.ID " +
                    "            AND C.DDID = :OLD_DEPT_ID), 0) SUB_STOCK, " +
                    "       NVL((SELECT KYS " +
                    "           FROM VIEW_ZCKYS C " +
                    "          WHERE C.SPID = A.ID " +
                    "            AND C.DDID = :OLD_DEPT_ID), 0) GENERAL_STOCK " +
                    "  FROM MAS_PRODUCT A, MAS_CATEGORY B, MAS_BRAND C " +
                    " WHERE A.CATEGORY_ID = B.ID AND A.BRAND_ID = C.ID" +
                    " AND A.PRODUCT_GROUP_ID IN ('10','20') " +
                    " AND A.STATE <> '3'" ;
        } else {
            Dept dept = SpringManager.getBean(DeptRepository.class).findOne(param.getString("NEW_DEPT_ID"));
            Location location = SpringManager.getBean(LocationService.class).findLocationByDept(dept, false);
            ServicePlanFactory.getServicePlanMethod().setDCParam(location, param, switched);
            sSQL += "SELECT A.ID,A.NAME, A.STATE,   " +
                    "       NVL((SELECT (C.USABLE_QTY +C.TRANSFER_RECEIVE_QTY + C.PURCHASING_WAY_QTY +   " +
                    "       C.TRANSFER_IN_BILLING_QTY + C.REQUIRE_BILLING_QTY +   " +
                    "        C.SALES_CONTROL_QTY - C.FROZEN_QTY)  STOCK_AMOUNT   " +
                    "           FROM ACC_STOCK C, MAS_LOCATION D, MAS_DEPOT E   " +
                    "          WHERE C.LOCATION_ID = D.ID   " +
                    "            AND D.USAGE_ID = '1'   " +
                    "            AND D.DEPOT_ID = E.ID   " +
                    "            AND E.DEPT_ID = :NEW_DEPT_ID   " +
                    "            AND C.PRODUCT_ID = A.ID AND D.VALID = 1), 0) STOCK_AMOUNT,   " +
                    "         NVL (" +
                    "     (SELECT SUM(T.USABLE_QTY)  " +
                    "        FROM ACC_STOCK T   " +
                    "       WHERE T.LOCATION_ID in :areaLocation  " +
                    "       AND T.PRODUCT_ID = A.ID " +
                    "       AND T.USABLE_QTY >= 0 ), 0) SUB_STOCK,   " +
                    " NVL((SELECT SUM(T.USABLE_QTY)    " +
                    "  FROM ACC_STOCK T   " +
                    " WHERE T.LOCATION_ID in :totalLocation    " +
                    "   AND T.PRODUCT_ID = A.ID  ), 0 ) GENERAL_STOCK   " +
                    "  FROM MAS_PRODUCT A, MAS_CATEGORY B, MAS_BRAND C " +
                    " WHERE A.CATEGORY_ID = B.ID AND A.BRAND_ID = C.ID" +
                    " AND A.PRODUCT_GROUP_ID IN ('10','20')   " +
                    " AND A.STATE <> '3'" ;
        }


        JSONArray array = param.getJSONArray("productList");
        if(array.size() > 100) throw new RuntimeException("程序有误，单次查询库存数量应小于等于100");
        sSQL = sSQL + " AND A.ID IN ( ";
        for(int i = 0;i<array.size();i++){
            JSONObject json = array.getJSONObject(i);
            if(i == 0){
                sSQL = sSQL + "'" +json.getString("productId")+ "'";
            }else{
                sSQL = sSQL + ",'" +json.getString("productId")+ "'";
            }
        }
        sSQL = sSQL + ")";
        return toJSONArray(find(sSQL, param));
    }

    public Double queryMainteinorderWorkTime(JSONObject param){
        String sSQL = " SELECT NVL(SUM(FLOOR(((B.CREATE_DATE + 0) - (A.CREATE_DATE + 0)) * 24 * 60)),0) WORKEDTIME  FROM SER_CONSTRUCTION_RECORD A, SER_CONSTRUCTION_RECORD B "
                + " WHERE A.ID = B.LAST_CONSTRUCTION_RECORD  AND B.STATE IN ('pause', 'finish') "
                + " AND B.MAINTENANCE_ORDER = :maintenanceOrderId ";
        JSONArray array = toJSONArray(find(sSQL, param));
        if(array.size() == 0) return 0.0;
        return array.getJSONObject(0).getDouble("WORKEDTIME");
    }

    public Double queryMainteinorderPauseTime(JSONObject param){
        String sSQL = " SELECT NVL(SUM(FLOOR(((B.CREATE_DATE + 0) - (A.CREATE_DATE + 0)) * 24 * 60)),0) WORKEDTIME  FROM SER_CONSTRUCTION_RECORD A, SER_CONSTRUCTION_RECORD B "
                + " WHERE A.ID = B.LAST_CONSTRUCTION_RECORD  AND B.STATE IN ('renew') "
                + " AND B.MAINTENANCE_ORDER = :maintenanceOrderId ";
        JSONArray array = toJSONArray(find(sSQL, param));
        if(array.size() == 0) return 0.0;
        return array.getJSONObject(0).getDouble("WORKEDTIME");
    }

    /**
     * 输入： asc
     * 输出： a%s%c
     * @param str
     * @return
     */
    public String regexSplit(String str){
        String result = "";
        for(int i = 0;i<str.length();i++){
            if(i==0){
                result = result + str.charAt(i) ;
            }else {
                result = result + "%" + str.charAt(i) ;
            }
        }
        return result;
    }


    public JSONArray queryInnerError(JSONObject param) {
        String sSQL = " SELECT B.ID "
                + " FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B "
                + " WHERE A.ID = B.SERVICE_PLAN_ID AND A.TERMINATION_DATE IS NULL AND A.CREATE_DATE >= :CLRQ " +
                " AND B.TERMINATION_DATE IS NULL AND B.SERVICE_STATE = 1 AND B.SALE_QTY > B.QTY_END  AND B.ASSIST_TYPE = 'ServicePlanItem.assistType.inner' "
                + " ORDER BY A.CREATE_DATE ASC " ;

        return toJSONArray(find(sSQL, param));
    }

    public JSONArray waitReceiveList(JSONObject param) {
        String sSQL = " SELECT * FROM ( "
                    + " SELECT A.FN_CUSTOMER_EXPAND_ID FNCUSTOMEREXPAND,C.CAR_LICENSE, C.REALNAME, C.CAR_MODEL_LABEL,C.JEKUN_MODEL_CODE,A.ESTIMATED_DELIVERY_DATE, B.SALE_QTY, B.QTY_RECEIVE,B.QTY_END, "
                    + " (SELECT Y.QTY_MGMT FROM MAS_PRODUCT X, MAS_PRODUCT_GROUP Y WHERE X.PRODUCT_GROUP_ID = Y.ID AND X.ID = B.PRODUCT_ID) QTY_MGMT, "
                    + " (SELECT Z.ESTIMATE_DATE FROM FN_PICK_UP_CAR Z WHERE Z.FN_CUSTOMER_EXPAND_ID = A.FN_CUSTOMER_EXPAND_ID AND A.BELONG_DEPT_ID = Z.DEPT_ID AND Z.STATE = 'approve' AND ROWNUM = 1) PICK_TIME, "
                    + " (SELECT Z.CREATE_DATE FROM FN_PICK_UP_CAR Z WHERE Z.FN_CUSTOMER_EXPAND_ID = A.FN_CUSTOMER_EXPAND_ID AND A.BELONG_DEPT_ID = Z.DEPT_ID AND Z.STATE = 'approve' AND ROWNUM = 1) P_C_DATE "
                    + " FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B,FN_CUSTOMER_EXPAND C "
                    + " WHERE A.ID = B.SERVICE_PLAN_ID AND A.FN_CUSTOMER_EXPAND_ID = C.ID AND B.SALE_QTY <> B.QTY_END AND B.SERVICE_STATE = 1 "
                    + " AND B.ASSIST_TYPE <> 'ServicePlanItem.assistType.outer' AND B.PRODUCT_RULE <> 1 AND A.SERVICE_DEPT_ID = :DEPTID  AND A.STATE IN ('confirm', 'qualityTest') "
                    + " ) M WHERE M.QTY_MGMT = 1 AND M.P_C_DATE IS NOT NULL ";

        return toJSONArray(find(sSQL, param));
    }

    public JSONArray receiveList(JSONObject param) {
        String sSQL = " SELECT * FROM ( "
                + " SELECT A.CONFIRM_DATE,A.FN_CUSTOMER_EXPAND_ID,D.CAR_LICENSE, D.CAR_MODEL_LABEL, D.JEKUN_MODEL_CODE,A.ID SERVICEPLAN_ID, B.ID SERVICEPLAN_ITEM_ID,B.PRODUCT_UOM_ID UOM_ID,  "
                + " (SELECT Z.NAME FROM MAS_PRODUCT_UOM Z WHERE Z.ID = B.PRODUCT_UOM_ID AND ROWNUM =1) UOM_NAME, "
                + " B.SALE_QTY,B.QTY_END, B.QTY_RECEIVE, B.PRODUCT_ID, C.NAME PRODUCT_NAME,C.BARCODE,B.LOCATION_ID, "
                + " (SELECT Y.QTY_MGMT FROM MAS_PRODUCT_GROUP Y WHERE Y.ID = C.PRODUCT_GROUP_ID) QTY_MGMT, "
                + " (SELECT A.NAME FROM MAS_LOCATION A WHERE A.ID = B.LOCATION_ID AND A.VALID = 1 AND ROWNUM = 1) LOCATION_NAME, "
                + " (SELECT Z.USABLE_QTY FROM ACC_STOCK Z WHERE Z.LOCATION_ID = B.LOCATION_ID AND Z.PRODUCT_ID = B.PRODUCT_ID) QTY_STOCK, A.STATE "
                + " FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B, MAS_PRODUCT C,FN_CUSTOMER_EXPAND D "
                + " WHERE A.ID = B.SERVICE_PLAN_ID AND B.PRODUCT_ID = C.ID AND A.FN_CUSTOMER_EXPAND_ID = D.ID AND B.SALE_QTY <> B.QTY_END AND B.ASSIST_TYPE <> 'ServicePlanItem.assistType.outer' AND B.PRODUCT_RULE <> 1 AND B.SERVICE_STATE = 1 "
                + " AND A.SERVICE_DEPT_ID = :DEPTID AND A.FN_CUSTOMER_EXPAND_ID = :FNCUSEXPANDID AND (select (select O.DEPT_ID from MAS_DEPOT O where o.ID = L.DEPOT_ID) from MAS_LOCATION L where l.ID=B.LOCATION_ID) =  A.SERVICE_DEPT_ID"
                + " AND A.STATE IN ('confirm', 'qualityTest') "
                + " ) M WHERE M.QTY_MGMT = 1  ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray receiveListFindOrder(JSONObject param) {
        String sSQL = " SELECT M.SERVICEPALN_ID FROM ( "
                    + " SELECT A.ID SERVICEPALN_ID,B.ID SERVICEPLAN_ITEM_ID,(SELECT Y.QTY_MGMT FROM MAS_PRODUCT_GROUP Y WHERE Y.ID = C.PRODUCT_GROUP_ID) QTY_MGMT "
                    + "  FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B,MAS_PRODUCT C "
                    + " WHERE A.ID = B.SERVICE_PLAN_ID AND B.PRODUCT_ID = C.ID AND B.SALE_QTY <> B.QTY_END AND B.ASSIST_TYPE <> 'ServicePlanItem.assistType.outer' "
                    + " AND A.SERVICE_DEPT_ID = :DEPTID AND A.FN_CUSTOMER_EXPAND_ID = :FNCUSEXPANDID AND A.STATE IN ('confirm', 'qualityTest') "
                    + " ) M,SER_MAINTENANCE_ORDER N "
                    + " WHERE M.SERVICEPLAN_ITEM_ID = N.SERVICE_PLAN_ITEM AND M.QTY_MGMT <> 1 AND N.LOCK_STATE = 1 ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray receiveListFinishWork(JSONObject param) {
        String sSQL = " SELECT * FROM ( "
                + " SELECT A.CONFIRM_DATE,A.FN_CUSTOMER_EXPAND_ID,D.CAR_LICENSE, D.CAR_MODEL_LABEL, D.JEKUN_MODEL_CODE,A.ID SERVICEPLAN_ID, B.ID SERVICEPLAN_ITEM_ID,  "
                + " B.SALE_QTY,B.QTY_END, B.QTY_RECEIVE, B.PRODUCT_ID, C.NAME PRODUCT_NAME,C.BARCODE,B.LOCATION_ID, "
                + " (SELECT Y.QTY_MGMT FROM MAS_PRODUCT_GROUP Y WHERE Y.ID = C.PRODUCT_GROUP_ID) QTY_MGMT, "
                + " (SELECT A.NAME FROM MAS_LOCATION A WHERE A.ID = B.LOCATION_ID AND A.VALID = 1 AND ROWNUM = 1) LOCATION_NAME, "
                + " (SELECT Z.USABLE_QTY FROM ACC_STOCK Z WHERE Z.LOCATION_ID = B.LOCATION_ID AND Z.PRODUCT_ID = B.PRODUCT_ID) QTY_STOCK "
                + " FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B, MAS_PRODUCT C,FN_CUSTOMER_EXPAND D "
                + " WHERE A.ID = B.SERVICE_PLAN_ID AND B.PRODUCT_ID = C.ID AND A.FN_CUSTOMER_EXPAND_ID = D.ID AND B.SALE_QTY <> B.QTY_END AND B.ASSIST_TYPE <> 'ServicePlanItem.assistType.outer' "
                +  " AND B.SALE_QTY - B.QTY_END <> B.QTY_RECEIVE AND B.RELEVANCE_WORK_ORDER = :RELEVANCE_WORK_ORDER "
                + " AND A.STATE IN ('confirm', 'qualityTest') "
                + " ) M WHERE M.QTY_MGMT = 1  ";
        return toJSONArray(find(sSQL, param));
    }


    public JSONArray receiveListCarInfo(JSONObject param) {
        String sSQL = "  SELECT A.FN_CUSTOMER_EXPAND_ID,B.CAR_LICENSE, B.CAR_MODEL_LABEL, B.JEKUN_MODEL_CODE FROM SER_SERVICE_PLAN A, FN_CUSTOMER_EXPAND B "
                + "  WHERE A.FN_CUSTOMER_EXPAND_ID = B.ID AND A.SERVICE_DEPT_ID = :DEPTID AND A.FN_CUSTOMER_EXPAND_ID = :FNCUSEXPANDID AND A.STATE IN ('confirm', 'qualityTest') AND ROWNUM = 1 ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray workDetailMaintenanceOrder(JSONObject param) {
        String sSQL = " SELECT A.ID ID_M,A.CREATE_DATE CREATE_DATE_M,A.POINT POINT_M, A.WORK_START_TIME WORK_START_TIME_M, A.WORKED_TIME WORKED_TIME_M, A.PAUSEED_TIME PAUSEED_TIME_M,A.POINT_START_TIME POINT_START_TIME_M, "
                    + " (SELECT R.CREATE_DATE FROM SER_CONSTRUCTION_RECORD R WHERE R.MAINTENANCE_ORDER = A.ID AND ROWNUM =1 ) WORK_START_DATE, "
                    + " (SELECT NVL(Z.SERVICE_HOURS,0) * 60 FROM MAS_PRODUCT_SERVICE_HOURS Z WHERE Z.PRODUCT_ID = A.PRODUCT_ID AND Z.VALID = 1 AND ROWNUM =1 ) PRODUCT_HOUR_M, "
                    + " A.STATE STATE_M,A.PRODUCT_RULE,(case when A.STATE = 'finish' then A.WORK_STATE when (case when A.PRODUCT_RULE = 1  THEN A.PRODUCT_RULE WHEN B.assist_type = 'ServicePlanItem.assistType.outer' THEN 1 ELSE 0 END ) = 0  THEN A.WORK_STATE ELSE A.DISPATCHE_STATE END) WORK_STATE_M,"
                    + "A.WORK_PLATFORM WORK_PLATFORM_M,DBMS_LOB.SUBSTR(A.WORKER1) WORKER1_M, "
                    + "       DBMS_LOB.SUBSTR(A.WORKER2) WORKER2_M, DBMS_LOB.SUBSTR(A.WORKER3) WORKER3_M, DBMS_LOB.SUBSTR(A.WORKER4) WORKER4_M,TO_CHAR(a.DISPATCH_BACK_DATE,'YYYY-MM-DD HH24:MI:SS')DISPATCH_BACK_DATE,"
                    + "A.SERVICE_PLAN_ITEM SERVICE_PLAN_ITEM_M,B.SERVICE_PLAN_ID SERVICE_PLAN_ID_M  "
                    + " FROM SER_MAINTENANCE_ORDER A, SER_SERVICE_PLAN_ITEM B, SER_SERVICE_PLAN C "
                    + " WHERE A.SERVICE_PLAN_ITEM = B.ID AND B.SERVICE_PLAN_ID = C.ID AND B.SERVICE_STATE = 1 AND A.LOCK_STATE = 0 AND A.BUSINESS_TYPE in ('common','inner')"
//                    + " AND B.PRODUCT_RULE <> :PRODUCT_RULE "
                    + " AND B.TERMINATION_DATE IS NULL AND B.ASSIST_TYPE in('ServicePlanItem.assistType.normal','ServicePlanItem.assistType.outer') AND C.STATE IN ('confirm', 'qualityTest') "
                    + " AND A.BELONG_DEPT_ID = :DEPT_ID AND A.FN_CUSTOMER_EXPAND_ID = :FN_CUSTOMER_EXPAND_ID AND (A.WORK_PLATFORM <> 'sprayPaint' OR '000007' <> (SELECT DEPT_TYPE FROM MAS_DEPT WHERE ID = :DEPT_ID) ) "
                    + " AND not exists (SELECT * FROM mas_brand t,mas_product m where t.id = m.brand_id and m.id = b.product_id and t.name = '富登') "
                    + " AND not exists (SELECT * FROM mas_brand t,mas_product m where t.id = m.brand_id and m.id = b.product_id and t.name = '师人订制' and C.channel in (select id from mas_channel s1 where name = '师人订制')) " +
                "";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray workDetailServiceplan(JSONObject param) {
        String sSQL = " SELECT A.ID SERVICE_PLAN_ID,A.CONFIRM_DATE,A.CONFIRM_UID,A.ESTIMATED_DELIVERY_DATE, A.CHANNEL , C.PRODUCT_GROUP_ID,"
                    + " B.ID SERVICE_PLAN_ITEM_ID,B.PRODUCT_RULE,(case when B.PRODUCT_RULE = 1 THEN B.PRODUCT_RULE WHEN B.assist_type = 'ServicePlanItem.assistType.outer' THEN 1 ELSE 0 END ) outer,  B.SALE_QTY,B.QTY_END, B.QTY_RECEIVE,A.STATE,B.RELEVANCE_WORK_ORDER,B.LOCATION_ID,B.SERVE_TYPE_ID, "
                    + " (SELECT A.NAME FROM MAS_LOCATION A WHERE A.ID = B.LOCATION_ID AND A.VALID = 1 AND ROWNUM = 1) LOCATION_NAME, "
                    + " (SELECT Z.USABLE_QTY FROM ACC_STOCK Z WHERE Z.LOCATION_ID = B.LOCATION_ID AND Z.PRODUCT_ID = B.PRODUCT_ID) QTY_STOCK, "
                    + " (SELECT Z.NAME FROM MAS_SERVE_TYPE Z WHERE Z.ID = B.SERVE_TYPE_ID AND Z.VALID = 1 AND ROWNUM =1 ) SERVE_TYPE_NAME, "
                    + " C.ID PRODUCT_ID, C.NAME PRODUCT_NAME,C.BARCODE, "
                    + " (SELECT Y.QTY_MGMT FROM MAS_PRODUCT_GROUP Y WHERE Y.ID = C.PRODUCT_GROUP_ID) QTY_MGMT, "
                    + " D.ID FN_CUSTOMER_EXPAND_ID,D.CAR_LICENSE, D.REALNAME,D.CAR_MODEL_LABEL, D.JEKUN_MODEL_CODE, D.MOBILE  "
                    + " FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B, MAS_PRODUCT C,FN_CUSTOMER_EXPAND D "
                    + "  WHERE A.ID = B.SERVICE_PLAN_ID AND B.PRODUCT_ID = C.ID AND A.FN_CUSTOMER_EXPAND_ID = D.ID AND B.SERVICE_STATE = 1 AND B.TERMINATION_DATE IS NULL AND A.STATE IN ('confirm', 'qualityTest') "
                    + " AND A.BELONG_DEPT_ID = :DEPT_ID AND A.FN_CUSTOMER_EXPAND_ID = :FN_CUSTOMER_EXPAND_ID "
                    + " AND not exists (SELECT * FROM mas_brand t where t.id = C.brand_id and t.name = '富登') "
                    + " AND not exists (SELECT * FROM mas_brand t where t.id = C.brand_id and t.name = '师人订制' and A.channel in (select s1.id from mas_channel s1 where name = '师人订制')) " +
                "";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray workDetailM2S(JSONObject param) {
        String sSQL = " SELECT B.ID,A.ID SERVICE_PLAN_ITEM,C.ID SERVICE_PLAN_ITEM_P,A.SALE_QTY,A.QTY_END,A.QTY_RECEIVE,A.PRODUCT_ID,(SELECT Z.NAME FROM MAS_PRODUCT Z WHERE Z.ID = A.PRODUCT_ID) PRODUCT_NAME "
                    + " FROM SER_SERVICE_PLAN_ITEM A,SER_MAINTENANCE_ORDER B,SER_SERVICE_PLAN_ITEM C,SER_SERVICE_PLAN D  "
                    + " WHERE A.RELEVANCE_WORK_ORDER = B.ID AND B.SERVICE_PLAN_ITEM = C.ID AND C.SERVICE_PLAN_ID = D.ID "
                    + " AND C.ASSIST_TYPE = 'ServicePlanItem.assistType.normal' AND B.LOCK_STATE = 0 AND D.STATE IN ('confirm', 'qualityTest') AND A.SERVICE_STATE = 1 "
                    + " AND D.BELONG_DEPT_ID = :DEPT_ID AND D.FN_CUSTOMER_EXPAND_ID = :FN_CUSTOMER_EXPAND_ID ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray waitQualityTest(JSONObject param) {
        String sSQL = " SELECT * FROM ( "
                    + " SELECT A.ID SERVICE_PLAN_ID, B.ID SERVICE_PLAN_ITEM_ID,A.FN_CUSTOMER_EXPAND_ID, B.SERVE_TYPE_ID,B.ASSIST_TYPE,B.NEED_QUALITY_FLAG, NVL(B.FINISH_DATE, SYSDATE) FINISH_DATE,A.ESTIMATED_DELIVERY_DATE, "
                    + " C.CAR_LICENSE,C.CAR_MODEL_LABEL,C.JEKUN_MODEL_CODE, "
                    + " (SELECT X.PRODUCT_GROUP_ID FROM MAS_PRODUCT X WHERE X.ID = B.PRODUCT_ID AND ROWNUM = 1) PRODUCT_GROUP_ID, "
                    + " (SELECT Z.PARENT_ITEM_ID FROM SER_SERVICE_PLAN_ITEM Z WHERE Z.PARENT_ITEM_ID = B.ID) PARENT_ITEM_ID, "
                    + " (SELECT Z.QUALITY_TEST_DATE FROM SER_SERVICE_PLAN_ITEM Z WHERE Z.PARENT_ITEM_ID = B.ID) CHILDQULITY, "
                    + " (SELECT Z.ID FROM FN_PICK_UP_CAR Z WHERE Z.FN_CUSTOMER_EXPAND_ID = A.FN_CUSTOMER_EXPAND_ID AND Z.STATE = 'approve' AND ROWNUM = 1) PICK_UP_ID "
                    + " FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B,FN_CUSTOMER_EXPAND C "
                    + " WHERE A.ID = B.SERVICE_PLAN_ID AND C.ID = A.FN_CUSTOMER_EXPAND_ID AND B.SERVICE_STATE = 1 AND B.TERMINATION_DATE IS NULL AND A.FINISH_WORK = 1 "
                    + " AND A.QUALITY_TEST_DATE IS NULL AND A.STATE = 'confirm' AND A.BELONG_DEPT_ID = :DEPTID "
                    + " and not exists(select 1 from SER_SERVICE_PLAN_ITEM Z, SER_SERVICE_PLAN_ITEM Z1 where z.SERVICE_PLAN_ID = A.ID and Z1.ID = Z.INNER_ITEM_ID and z1.QUALITY_TEST_DATE is null and Z1.TERMINATION_DATE is null) "
                    + " ) M WHERE (M.PRODUCT_GROUP_ID = '20' OR M.ASSIST_TYPE = 'ServicePlanItem.assistType.outer' OR M.NEED_QUALITY_FLAG = 1) AND (M.PARENT_ITEM_ID IS NULL OR (M.PARENT_ITEM_ID IS NOT NULL AND CHILDQULITY IS NOT NULL)) ";
        return toJSONArray(find(sSQL, param));
    }


    public JSONArray waitQualityTestDetail(JSONObject param) {
        String sSQL = " SELECT * FROM ( "
                + " SELECT A.ID SERVICE_PLAN_ID, B.ID SERVICE_PLAN_ITEM_ID,A.FN_CUSTOMER_EXPAND_ID, B.SERVE_TYPE_ID, B.ASSIST_TYPE,B.NEED_QUALITY_FLAG, NVL(B.FINISH_DATE, SYSDATE) FINISH_DATE, "
                + " A.ESTIMATED_DELIVERY_DATE, B.QUALITY_FLAG,B.QUALITY_TEST_DATE,B.QUALITY_TEST_UID,C.ID PRODUCT_ID,C.NAME PRODUCT_NAME, "
                + " (SELECT X.PRODUCT_GROUP_ID FROM MAS_PRODUCT X WHERE X.ID = C.ID AND ROWNUM = 1) PRODUCT_GROUP_ID, "
                + " (SELECT X.NAME FROM MAS_PARTY X, MAS_PARTY_ROLE Y WHERE X.ID = Y.PARTY_ID AND Y.ID = B.QUALITY_TEST_UID AND ROWNUM = 1) QUALITY_TEST_U_NAME, "
                + " (SELECT Z.PARENT_ITEM_ID FROM SER_SERVICE_PLAN_ITEM Z WHERE Z.PARENT_ITEM_ID = B.ID) PARENT_ITEM_ID, "
                + " (SELECT Z.QUALITY_TEST_DATE FROM SER_SERVICE_PLAN_ITEM Z WHERE Z.PARENT_ITEM_ID = B.ID) CHILDQULITY, "
                + " (SELECT DBMS_LOB.SUBSTR(Z.WORKER1) FROM SER_MAINTENANCE_ORDER Z WHERE Z.SERVICE_PLAN_ITEM = B.ID AND B.ASSIST_TYPE = 'ServicePlanItem.assistType.normal' AND ROWNUM =1) WORKER1_M, "
                + " (SELECT DBMS_LOB.SUBSTR(Z.WORKER2) FROM SER_MAINTENANCE_ORDER Z WHERE Z.SERVICE_PLAN_ITEM = B.ID AND B.ASSIST_TYPE = 'ServicePlanItem.assistType.normal' AND ROWNUM =1) WORKER2_M, "
                + " (SELECT DBMS_LOB.SUBSTR(Z.WORKER3) FROM SER_MAINTENANCE_ORDER Z WHERE Z.SERVICE_PLAN_ITEM = B.ID AND B.ASSIST_TYPE = 'ServicePlanItem.assistType.normal' AND ROWNUM =1) WORKER3_M, "
                + " (SELECT DBMS_LOB.SUBSTR(Z.WORKER4) FROM SER_MAINTENANCE_ORDER Z WHERE Z.SERVICE_PLAN_ITEM = B.ID AND B.ASSIST_TYPE = 'ServicePlanItem.assistType.normal' AND ROWNUM =1) WORKER4_M "
                + " FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B, MAS_PRODUCT C "
                + " WHERE A.ID = B.SERVICE_PLAN_ID AND B.PRODUCT_ID = C.ID AND B.SERVICE_STATE = 1 AND A.FINISH_WORK = 1 AND B.TERMINATION_DATE IS NULL AND A.FN_CUSTOMER_EXPAND_ID = :FN_CUSTOMER_EXPAND_ID AND A.BELONG_DEPT_ID = :DEPT_ID AND A.STATE IN ('confirm', 'qualityTest') "
                + " and not exists(select 1 from SER_SERVICE_PLAN_ITEM Z, SER_SERVICE_PLAN_ITEM Z1 where z.SERVICE_PLAN_ID = A.ID and Z1.ID = Z.INNER_ITEM_ID and z1.QUALITY_TEST_DATE is null and Z1.TERMINATION_DATE is null)"
                + " ) M WHERE (M.PRODUCT_GROUP_ID = '20' OR M.ASSIST_TYPE = 'ServicePlanItem.assistType.outer' OR M.NEED_QUALITY_FLAG = 1) AND (M.PARENT_ITEM_ID IS NULL OR (M.PARENT_ITEM_ID IS NOT NULL AND CHILDQULITY IS NOT NULL)) ";
        return toJSONArray(find(sSQL, param));
    }


    public JSONArray findPlanItemForProductReceive(JSONObject param){
        String sSQL = "SELECT A.ID, B.PRODUCT_ID, C.NAME " +
                "  FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B, MAS_PRODUCT C, MAS_PRODUCT_GROUP G " +
                " WHERE A.ID = B.SERVICE_PLAN_ID " +
                "   AND B.PRODUCT_ID = C.ID " +
                "   AND C.PRODUCT_GROUP_ID = G.ID " +
                "   AND B.SERVICE_STATE = 1 " +
                "   AND B.TERMINATION_DATE IS NULL " +
                "   AND A.FN_CUSTOMER_EXPAND_ID = :FN_CUSTOMER_EXPAND_ID " +
                "   AND A.BELONG_DEPT_ID = :DEPT_ID " +
                "   AND (B.FINISH_DATE IS NULL OR B.TERMINATION_DATE IS NULL) " +
                "   AND G.ID = '10' " +
                "   AND A.STATE = 'confirm' " +
                "   AND NOT EXISTS (SELECT 1 " +
                "          FROM SER_SERVICE_PLAN_ITEM Z, SER_SERVICE_PLAN_ITEM Z1 " +
                "         WHERE Z.SERVICE_PLAN_ID = A.ID " +
                "           AND Z1.ID = Z.INNER_ITEM_ID " +
                "           AND Z1.QUALITY_TEST_DATE IS NULL " +
                "           AND Z1.TERMINATION_DATE IS NULL)";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray findServicePlansForPDA(JSONObject param) {
        String sSQL = " SELECT DISTINCT A.ID FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B, MAS_PRODUCT C "
                    + " WHERE A.ID = B.SERVICE_PLAN_ID AND B.PRODUCT_ID = C.ID "
                    + " AND A.BELONG_DEPT_ID = A.SERVICE_DEPT_ID AND B.SERVICE_STATE = 1 "
                    + " AND B.SALE_QTY > (B.QTY_RECEIVE + B.QTY_END) AND C.PRODUCT_GROUP_ID <> 20 AND A.STATE = 'confirm' "
                    + " AND A.SERVICE_DEPT_ID in (:deptId) ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray vehicleCarInfo(JSONObject param) {
        String sSQL = " SELECT A.ID,A.STATE CAR_STATE, A.DEPT_ID CAR_DEPT_ID,A.FN_CUSTOMER_EXPAND_ID CAR_FN_CUSTOMER_EXPAND_ID "
                    + " FROM FN_PICK_UP_CAR A WHERE A.STATE = 'approve' AND A.DEPT_ID = :DEPTID AND A.FN_CUSTOMER_EXPAND_ID = :FNCUSTOMEREXPANDID  ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray vehicleInnerPickDelUpCarInfo(JSONObject param) {
        String sSQL = "SELECT T.ID,T.STATE CAR_STATE FROM FN_INNER_PICK_DEL_UP_CAR T WHERE T.FN_CUSTOMER_EXPAND_ID = :FNCUSTOMEREXPANDID AND T.BELONG_DEPT_ID = :DEPTID AND T.STATE NOT IN ('confirm','repeal')";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray vehicleInnerPickDelUpCarInfoByOtherSideDept(JSONObject param) {
        String sSQL = "SELECT T.ID,T.STATE CAR_STATE FROM FN_INNER_PICK_DEL_UP_CAR T WHERE T.FN_CUSTOMER_EXPAND_ID = :FNCUSTOMEREXPANDID AND T.OTHER_SIDE_DEPT_ID = :DEPTID AND T.STATE NOT IN ('confirm','repeal')";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray hasNotEndServicePlan(JSONObject param) {
        String sSQL = " select * " +
                " from SER_SERVICE_PLAN a " +
                " where a.FN_CUSTOMER_EXPAND_ID = :FNCUSTOMEREXPANDID " +
                "  and a.STATE not in ('termination','receivables')";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray validDocuments(JSONObject param) {
        String sSQL = "SELECT B.ID  " +
                "  FROM FN_CUSTOMER_EXPAND A, SER_SERVICE_PLAN B  " +
                "  WHERE A.ID = B.FN_CUSTOMER_EXPAND_ID  " +
                "  AND A.ID = :FNCUSTOMEREXPANDID AND B.BELONG_DEPT_ID = :DEPTID AND B.STATE  NOT IN ('termination','receivables')  " +
                "  AND (B.CREATE_DATE >= (SELECT MAX(M.CREATE_DATE) FROM fn_pick_up_car M WHERE M.FN_CUSTOMER_EXPAND_ID = A.ID) or B.state in ('qualityTest','confirm'))  " +
                "  UNION ALL  " +
                "  SELECT B.ID  " +
                "  FROM FN_CUSTOMER_EXPAND A ,SER_LOSS_QUOTATION B  " +
                "  WHERE A.ID = B.CUSTOMER_EXPAND_ID  " +
                "  AND A.ID = :FNCUSTOMEREXPANDID AND B.FIXED_LOSS_DEPT = :DEPTID AND B.STATE NOT IN ('repeal','suspend')  " +
                "  UNION ALL  " +
                "  SELECT B.ID  " +
                "  FROM FN_CUSTOMER_EXPAND A,SER_CAR_ACCIDENT B   " +
                "  WHERE A.ID = B.CUSTOMER_EXPAND_ID  " +
                "  AND A.ID = :FNCUSTOMEREXPANDID AND B.DEPT_ID = :DEPTID AND B.STATE <> 'repeal' ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray planByConfirmQualityTest(JSONObject param) {
        String sSQL = "SELECT B.ID  " +
                "  FROM FN_CUSTOMER_EXPAND A, SER_SERVICE_PLAN B  " +
                "  WHERE A.ID = B.FN_CUSTOMER_EXPAND_ID  " +
                "  AND A.ID = :FNCUSTOMEREXPANDID AND B.BELONG_DEPT_ID = :DEPTID AND B.STATE IN ('confirm','qualityTest')  " +
                " AND EXISTS (SELECT * FROM Ser_Service_Plan_Item m ,ser_maintenance_order n  " +
                "                      where m.id = n.service_plan_item and m.service_plan_id = b.id and n.work_start_time is not null) ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray validPlans(JSONObject param) {
        String sSQL = "SELECT B.ID  " +
                " FROM FN_CUSTOMER_EXPAND A, SER_SERVICE_PLAN B  " +
                " WHERE A.ID = B.FN_CUSTOMER_EXPAND_ID   " +
                " AND A.ID = :FNCUSTOMEREXPANDID AND B.BELONG_DEPT_ID = :DEPTID  " +
                " AND B.STATE <> 'termination' ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray receivablesPlans(JSONObject param) {
        String sSQL = "SELECT B.ID  " +
                " FROM FN_CUSTOMER_EXPAND A, SER_SERVICE_PLAN B  " +
                " WHERE A.ID = B.FN_CUSTOMER_EXPAND_ID   " +
                " AND A.ID = :FNCUSTOMEREXPANDID AND B.BELONG_DEPT_ID = :DEPTID  " +
                " AND B.STATE = 'receivables' ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray vehiclePlanInfo(JSONObject param) {
        String sSQL = " SELECT  A.ID,A.STATE,A.FINISH_WORK,A.CONFIRM_DATE,A.QUALITY_TEST_DATE,B.FITTING,B.SALE_QTY,B.QTY_END,B.QTY_RECEIVE,B.FINISH_DATE FINISH_DATE_ITEM,B.QUALITY_TEST_DATE QUALITY_TEST_DATE_ITEM, "
                    + " NVL((SELECT Z.LOCK_STATE FROM SER_MAINTENANCE_ORDER Z WHERE Z.SERVICE_PLAN_ITEM = B.ID AND ROWNUM = 1),2) LOCK_STATE ,"
                    + " (SELECT COUNT(*) FROM SER_MAINTENANCE_ORDER Z WHERE Z.SERVICE_PLAN_ITEM = B.ID AND Z.work_start_time IS NOT NULL) WORK_COUNT "
                    + " FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B  "
                    + " WHERE A.ID = B.SERVICE_PLAN_ID AND A.STATE NOT IN ('receivables', 'termination') AND B.SERVICE_STATE = 1 AND B.TERMINATION_DATE IS NULL AND A.BELONG_DEPT_ID = :DEPTID AND A.FN_CUSTOMER_EXPAND_ID = :FNCUSTOMEREXPANDID AND ROWNUM < 5000 ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray vehiclePlanReci(JSONObject param) {
        String sSQL = " SELECT COUNT(*) AMOUNT FROM SER_SERVICE_PLAN B  "
                +" WHERE B.FN_CUSTOMER_EXPAND_ID = :FN_CUSTOMER_EXPAND_ID AND B.BELONG_DEPT_ID = :DEPT_ID "
                +" AND B.RECEIVABLE_DATE >=(SELECT Z.CREATE_DATE FROM FN_PICK_UP_CAR Z WHERE Z.FN_CUSTOMER_EXPAND_ID = :FN_CUSTOMER_EXPAND_ID AND Z.DEPT_ID = :DEPT_ID AND Z.STATE = 'approve' AND ROWNUM = 1) ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray vehicleInnerPickUpCar(JSONObject param) {
        String sSQL = " SELECT STATE FROM (   "
                +" SELECT A.STATE FROM FN_INNER_PICK_DEL_UP_CAR A  "
                + " WHERE A.FN_CUSTOMER_EXPAND_ID = :FN_CUSTOMER_EXPAND_ID AND A.BELONG_DEPT_ID = :BELONG_DEPT_ID AND A.PICK_UP_CAR = :PICK_UP_CARS ORDER BY A.CREATE_DATE DESC "
                + "  ) WHERE ROWNUM = 1  ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray findForJudgeReturnOne(JSONObject param) {
        String sSQL = " SELECT DISTINCT D.SERVICE_PLAN_ID FROM STOCK_PRODUCT_RETURN A, STOCK_PRODUCT_RETURN_ITEM B,STOCK_PRODUCT_RECEIVE_ITEM C, SER_SERVICE_PLAN_ITEM D "
                    + " WHERE A.ID = B.PRODUCT_RETURN_ID AND B.PRODUCT_RECEIVE_ITEM_ID = C.ID AND C.SERVICE_PLAN_ITEM_ID = D.ID AND A.STATE = 'approve' AND A.ID = :ID ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray findForJudgeReturnTwo(JSONArray array) {
        JSONObject param = new JSONObject();
        String sql = " AND A.SERVICE_PLAN_ID IN ( ";
        for(int i = 0;i<array.size() ;i++ ){

            if(i ==0){
                sql = sql + "'" + array.get(i) + "'";
            }else{
                sql = sql + ",'" + array.get(i) + "'";
            }
        }
        sql = sql + " ) ";
        String sSQL = " SELECT * FROM ( "
                + " SELECT A.SERVICE_PLAN_ID, A.ID, A.PRODUCT_ID, "
                + " (SELECT Z.QTY_RECEIVE FROM SER_SERVICE_PLAN_ITEM Z  WHERE Z.ID = A.ID) QTY_RECEIVE_S, "
                + " SUM(B.QTY_RECEIVE) - SUM(B.QTY_RETURNED) QTY_RECEIVE_R "
                + " FROM SER_SERVICE_PLAN_ITEM A, STOCK_PRODUCT_RECEIVE_ITEM B,STOCK_PRODUCT_RECEIVE C "
                + " WHERE B.SERVICE_PLAN_ITEM_ID = A.ID AND C.ID = B.PRODUCT_RECEIVE_ID AND C.STATE = 'approve' "
                + sql
                + "  GROUP BY A.SERVICE_PLAN_ID, A.ID, A.PRODUCT_ID "
                + " ) Z WHERE Z.QTY_RECEIVE_S <> QTY_RECEIVE_R ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray findStockByProduct (JSONObject param) {
        String sSQL = "SELECT " +
                "       NVL((SELECT KYS " +
                "              FROM VIEW_QYCKYS C " +
                "             WHERE C.SPID = A.ID " +
                "               AND C.DDID = :OLD_DEPT_ID), " +
                "            0) OLD_SUB_STOCK, " +
                "       NVL((SELECT T.USABLE_QTY " +
                "              FROM ACC_STOCK T " +
                "             WHERE T.LOCATION_ID = :areaLocationId " +
                "               AND T.PRODUCT_ID = A.ID " +
                "               AND T.USABLE_QTY >= 0), " +
                "            0) NEW_SUB_STOCK, " +
                "       NVL((SELECT KYS " +
                "              FROM VIEW_ZCKYS C " +
                "             WHERE C.SPID = A.ID " +
                "               AND C.DDID = :OLD_DEPT_ID), " +
                "            0) OLD_GENERAL_STOCK, " +
                "       NVL((SELECT T.USABLE_QTY " +
                "              FROM ACC_STOCK T " +
                "             WHERE T.LOCATION_ID = :totalLocationId " +
                "               AND T.PRODUCT_ID = A.ID), " +
                "            0) NEW_GENERAL_STOCK," +
                "       (SELECT COMPANY_ID FROM MAS_LOCATION D WHERE D.ID = :areaLocationId) AS SUB_STOCK_COMPANY_ID, " +
                "       (SELECT F.ABBREVIATION  " +
                "           FROM MAS_LOCATION D, MAS_PARTY_ROLE E, MAS_PARTY F " +
                "          WHERE D.ID = :areaLocationId " +
                "            AND D.COMPANY_ID = E.ID " +
                "            AND E.PARTY_ID = F.ID) SUB_STOCK_COMPANY_NAME, " +
                "       (SELECT COMPANY_ID FROM MAS_LOCATION D WHERE D.ID = :totalLocationId) AS GENERAL_STOCK_COMPANY_ID, " +
                "       (SELECT F.NAME " +
                "           FROM MAS_LOCATION D, MAS_PARTY_ROLE E, MAS_PARTY F " +
                "          WHERE D.ID = :totalLocationId " +
                "            AND D.COMPANY_ID = E.ID " +
                "            AND E.PARTY_ID = F.ID) GENERAL_STOCK_COMPANY_NAME , " +
                "       (SELECT ID FROM MAS_PARTY_ROLE D WHERE D.ID = '1001') AS COMPANY_ID, " +
                "       (SELECT F.ABBREVIATION " +
                "           FROM MAS_PARTY_ROLE E, MAS_PARTY F " +
                "          WHERE  E.ID = '1001'  " +
                "            AND E.PARTY_ID = F.ID) COMPANY_NAME  " +
                "  FROM MAS_PRODUCT A, MAS_CATEGORY B, MAS_BRAND C " +
                " WHERE A.CATEGORY_ID = B.ID " +
                "   AND A.BRAND_ID = C.ID " +
                "   AND A.PRODUCT_GROUP_ID IN ('10', '20') " +
                "   AND A.STATE <> '3'  " +
                "   AND A.ID = :PRODUCT_ID";
        return toJSONArray(find(sSQL, param));
    }

    /***
     * 拼接查询的SQL 并返回分页的数据
     */
    public JSONArray queryProductBySale(JSONObject param) {
        String sSQL = "SELECT G.* " +
                "  FROM (SELECT G.*, ROWNUM RN, CEIL(TOTAL / :SIZE) PAGE " +
                "           FROM (SELECT MAX(ROWNUM) OVER() TOTAL, ID, STOCK_AMOUNT, SUB_STOCK,GENERAL_STOCK,USABLE_QTY,FROZEN_QTY,TRANSFER_RECEIVE_QTY,PURCHASING_WAY_QTY,TRANSFER_IN_BILLING_QTY,REQUIRE_BILLING_QTY " +
                "                    FROM ("+ queryGoodsStock(param) +") " +
                "                   WHERE 1 = 1" ;

        sSQL += "ORDER BY STOCK_AMOUNT DESC, SUB_STOCK DESC, GENERAL_STOCK DESC) G " +
                "          WHERE ROWNUM <= DECODE(0, :PAGE, :SIZE, (:PAGE + 1) * :SIZE)) G " +
                " WHERE RN > (:PAGE) * :SIZE";
        return toJSONArray(find(sSQL, param));
    }

    // 核心sSQL
    private String queryGoodsStock(JSONObject param) {
        String sSQL = "";

        sSQL += "SELECT A.ID, A.STATE, A.BRAND_ID, A.CLASSIFY_ID, A.CATEGORY_ID,  " +
                "       NVL((SELECT (C.USABLE_QTY + C.TRANSFER_RECEIVE_QTY + C.PURCHASING_WAY_QTY + " +
                "       C.TRANSFER_IN_BILLING_QTY + C.REQUIRE_BILLING_QTY + " +
                "        C.SALES_CONTROL_QTY - C.FROZEN_QTY)  STOCK_AMOUNT " +
                "           FROM ACC_STOCK C, MAS_LOCATION D " +
                "          WHERE C.LOCATION_ID = D.ID " +
                "            AND D.USAGE_ID = '1' " +
                "            AND D.ID = :NEW_LOCATION_ID " +
                "            AND C.PRODUCT_ID = A.ID AND D.VALID = 1), 0) STOCK_AMOUNT, "+
                "            NVL((SELECT c.USABLE_QTY  "+
                "              FROM ACC_STOCK C, MAS_LOCATION D "+
                "            WHERE C.LOCATION_ID = D.ID "+
                "              AND D.USAGE_ID = '1' "+
                "            AND D.ID = :NEW_LOCATION_ID " +
                "              AND C.PRODUCT_ID = A.ID "+
                "              AND D.VALID = 1), "+
                "           0) USABLE_QTY, "+
                "            NVL((SELECT c.FROZEN_QTY  "+
                "              FROM ACC_STOCK C, MAS_LOCATION D "+
                "            WHERE C.LOCATION_ID = D.ID "+
                "              AND D.USAGE_ID = '1' "+
                "            AND D.ID = :NEW_LOCATION_ID " +
                "              AND C.PRODUCT_ID = A.ID "+
                "              AND D.VALID = 1), "+
                "           0) FROZEN_QTY, "+
                "           NVL((SELECT c.TRANSFER_RECEIVE_QTY "+
                "             FROM ACC_STOCK C, MAS_LOCATION D "+
                "            WHERE C.LOCATION_ID = D.ID "+
                "              AND D.USAGE_ID = '1' "+
                "            AND D.ID = :NEW_LOCATION_ID " +
                "              AND C.PRODUCT_ID = A.ID "+
                "              AND D.VALID = 1), "+
                "           0) TRANSFER_RECEIVE_QTY, "+
                "           NVL((SELECT c.PURCHASING_WAY_QTY "+
                "             FROM ACC_STOCK C, MAS_LOCATION D "+
                "            WHERE C.LOCATION_ID = D.ID "+
                "              AND D.USAGE_ID = '1' "+
                "            AND D.ID = :NEW_LOCATION_ID " +
                "              AND C.PRODUCT_ID = A.ID "+
                "              AND D.VALID = 1), "+
                "           0) PURCHASING_WAY_QTY, "+
                "           NVL((SELECT c.TRANSFER_IN_BILLING_QTY "+
                "             FROM ACC_STOCK C, MAS_LOCATION D "+
                "            WHERE C.LOCATION_ID = D.ID "+
                "              AND D.USAGE_ID = '1' "+
                "            AND D.ID = :NEW_LOCATION_ID " +
                "              AND C.PRODUCT_ID = A.ID "+
                "              AND D.VALID = 1), "+
                "           0) TRANSFER_IN_BILLING_QTY, "+
                "           NVL((SELECT c.REQUIRE_BILLING_QTY "+
                "             FROM ACC_STOCK C, MAS_LOCATION D "+
                "            WHERE C.LOCATION_ID = D.ID "+
                "              AND D.USAGE_ID = '1' "+
                "            AND D.ID = :NEW_LOCATION_ID " +
                "              AND C.PRODUCT_ID = A.ID "+
                "              AND D.VALID = 1), "+
                "           0) REQUIRE_BILLING_QTY, " +
                "   null SUB_STOCK,null GENERAL_STOCK " +
                "  FROM MAS_PRODUCT A, MAS_CATEGORY B, MAS_BRAND C " +
                " WHERE A.CATEGORY_ID = B.ID AND A.BRAND_ID = C.ID " +
                " AND A.PRODUCT_GROUP_ID IN ('10','20','50') " +
                " AND A.STATE <> '3'";
        // 界面传入条件
        if(param.get("PRODUCT_TEMP") != null){
            sSQL += "AND ( A.ID = :PRODUCT_TEMP OR A.NAME LIKE '%"+ regexSplit(param.getString("PRODUCT_TEMP")) + "%' OR B.ID = :PRODUCT_TEMP OR B.NAME LIKE '%"+ param.getString("PRODUCT_TEMP") + "%' )";
        }
        //分销单的条件
        if (param.get("PRODUCT_CLASSIFY") != null && !StringUtils.isEmpty(param.getString("PRODUCT_CLASSIFY"))) {
            sSQL += "  AND A.CLASSIFY_ID =:PRODUCT_CLASSIFY ";
        }
        if (param.get("PRODUCT_BRAND") != null && !StringUtils.isEmpty(param.getString("PRODUCT_BRAND"))) {
            sSQL += " AND A.BRAND_ID =:PRODUCT_BRAND ";
        }
        sSQL += " AND ROWNUM < 1000";
        return sSQL;
    }


    public JSONArray queryPayDetails(JSONObject param){
        String sSQL = "select A.ID,A.INNER_BUSINESS_TYPE,C.NAME payment_method,B.AMOUNT,to_char(b.CREATE_DATE,'yyyy-MM-dd HH:mm:ss')CREATE_DATE,GET_PARTY_NAME(A.CREATOR_ID,'Employee')CREATOR_NAME " +
                "from ADVANCE_PAYMENT a,advance_payment_method b,mas_payment_method c " +
                "where a.id = b.PAYMENT_ID " +
                "  and b.PAYMENT_METHOD_TYPE = c.id " +
                "and REMARK=:ID " +
                "union all " +
                "select A.ID,A.INNER_BUSINESS_TYPE,C.NAME,B.AMOUNT,to_char(b.CREATE_DATE,'yyyy-MM-dd HH:mm:ss'),GET_PARTY_NAME(a.CLERK_ID,'Employee')CREATOR_NAME " +
                "from RETAIL_ORDER a " +
                "    left join Retail_Order_Payment_Method b on a.ID = b.RETAIL_ORDER_ID " +
                "    left join mas_payment_method c on b.PAYMENT_METHOD_TYPE = c.id " +
                "where  (A.ORIGIN= :ID or SOURCE=:ID)";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray queryWorkTC(JSONObject param){
        String sSQL = "select PROCESS,sum(TC_AMT)TC_AMT,nvl(max(WORKER),'--')WORKER " +
                "from (select PROCESS, " +
                "             sum(TC_AMT)                                                                      TC_AMT, " +
                "             listagg(GET_PARTY_NAME(WORKER, 'Employee'), ';') within group (order by PROCESS) WORKER " +
                "      from (select PROCESS, " +
                "                   sum(TC_AMT) TC_AMT, " +
                "                   WORKER " +
                "            from TC_RYSGTC A " +
                "            where SERVICE_PLAN = :ID " +
                "            group by PROCESS, WORKER) A " +
                "      group by PROCESS " +
                "      union all " +
                "      select 1, 0, '' " +
                "      from DUAL " +
                "      union all " +
                "      select 2, 0, '' " +
                "      from DUAL " +
                "      union all " +
                "      select 3, 0, '' " +
                "      from DUAL " +
                "      union all " +
                "      select 4, 0, '' " +
                "      from DUAL) " +
                "group by PROCESS ";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray querySalerTC(JSONObject param){
        String sSQL = "select sum(MAIN_SALESMAN_TCJE)MAIN_SALESMAN_TCJE,sum(SUB_SALESMAN_TCJE)SUB_SALESMAN_TCJE " +
                "from TC_XSETC " +
                "where SOURCE=:ID";
        return toJSONArray(find(sSQL, param));
    }

    public JSONObject queryPlanDetails(JSONObject param){
        String sSQL = "select max(QUALITY_TEST_DATE)QUALITY_TEST_DATE,max(CREATE_DATE)CREATE_DATE,max(CHANNEL_NAME)CHANNEL_NAME,max(CREATOR)CREATOR, " +
                "       LISTAGG(QUALITY_TEST_NAME,';')WITHIN GROUP ( ORDER BY QUALITY_TEST_DATE)QUALITY_TEST_NAME " +
                "from (select distinct nvl(TO_CHAR(A.QUALITY_TEST_DATE, 'YYYY-MM-DD HH24:MI:SS'), '--') QUALITY_TEST_DATE, " +
                "             TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS')                  CREATE_DATE, " +
                "             (select c.name from MAS_CHANNEL C where c.ID = A.CHANNEL)        CHANNEL_NAME, " +
                "             nvl(GET_PARTY_NAME(b.QUALITY_TEST_UID, 'Employee'), '--')        QUALITY_TEST_NAME, " +
                "             nvl(GET_PARTY_NAME(CREATOR_ID, 'Employee'), '--')                CREATOR " +
                "      from SER_SERVICE_PLAN a, " +
                "           ser_service_plan_item b " +
                "      where a.ID = :ID " +
                "        and a.id = b.SERVICE_PLAN_ID " +
                "    )X";
        return (JSONObject) toJSONArray(find(sSQL, param)).stream().findAny().orElse(null);
    }


    public JSONArray queryPlanItemAmount(JSONObject param){
        String sSQL = "select (select '[' || p.ID || ']' || p.NAME from MAS_PRODUCT p where p.id = C.PRODUCT_ID)      PRODUCT, " +
                "       C.PRIMARY_PRICE_UNIT_SHOW, C.CARRIER_ID, C.CARRIER_NAME, " +
                "       C.SALES_AMOUNT, " +
                "       c.PFT_PRICE_UNIT, " +
                "       SALE_QTY, " +
                "       sum(nvl(f.COUPON_PROFIT_AMOUNT, 0))                                                        COUPON_AMOUNT, " +
                "       sum(nvl(f.COUPON_DISCOUNT_AMOUNT, 0))                                       COUPON_DISCOUNT_AMOUNT, " +
                "       sum(nvl(RED_ENVELOPE_CASH, 0))                                                               RED_ENVELOPE_CASH, " +
                "       sum(nvl(BAOBEI_ENVELOPE_CASH, 0))                                                            BAOBEI_ENVELOPE_CASH, " +
                "       sum(nvl(f.DISCOUNT_AMOUNT, 0))                                                               DISCOUNT_AMOUNT, " +
                "       sum(case when c.CROSSIC_ID is not null then C.SALES_AMOUNT - c.REAL_PAY_AMOUNT else 0 end) cross_discount, " +
                "       C.SALES_AMOUNT-sum(  nvl(f.DISCOUNT_AMOUNT, 0))                                      REAL_PAY_AMOUNT " +
                "from SER_SERVICE_PLAN A, " +
                "     SER_SERVICE_PLAN_item c " +
                "         left JOIN RETAIL_ORDER_ITEM D on c.id = D.ORIGINNUMBER " +
                "         left join RETAIL_ORDER_DISCOUNT F on F.RETAIL_ITEM_ID = D.id " +
                "         left JOIN Retail_Order_Extension E on e.RETAIL_ORDER_ITEM_ID = d.ID " +
                "WHERE a.ID = :ID " +
                "  and A.id = c.SERVICE_PLAN_ID " +
                "group by C.PRIMARY_PRICE_UNIT_SHOW,C.SALES_AMOUNT,c.PFT_PRICE_UNIT,SALE_QTY,C.PRODUCT_ID, C.CARRIER_ID, C.CARRIER_NAME ";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 工作台统计门店服务方案状态
     * @param param  需要state(方案状态)、deptId(门店id)
     * @return  total(总数)、gtCreateDate(最小时间)、ltCreateDate(最大时间)
     */
    public JSONObject queryCountByState(JSONObject param) {
        StringBuilder totalSQL = new StringBuilder();
        StringBuilder gtCreateDateSQL = new StringBuilder();
        StringBuilder ltCreateDateSQL = new StringBuilder();
        // 封装sql
        totalSQL.append("SELECT count(1) total FROM ser_service_Plan A WHERE A.STATE = :state ");
        gtCreateDateSQL.append("SELECT A.CREATE_DATE gtCreateDate FROM ser_service_Plan A WHERE A.STATE = :state ");
        ltCreateDateSQL.append("SELECT A.CREATE_DATE ltCreateDate FROM ser_service_Plan A WHERE A.STATE = :state ");
        if (param.containsKey("deptId")){
            totalSQL.append(" AND A.BELONG_DEPT_ID = :deptId");
            gtCreateDateSQL.append(" AND A.BELONG_DEPT_ID = :deptId");
            ltCreateDateSQL.append(" AND A.BELONG_DEPT_ID = :deptId");
        }
        gtCreateDateSQL.append("  ORDER BY A.CREATE_DATE");
        ltCreateDateSQL.append("  ORDER BY A.CREATE_DATE DESC");
        // 执行sql
        Integer total = toJSONArray(find(totalSQL.toString(), param)).getJSONObject(0).getInteger("TOTAL");
        Date gtCreateDate = toJSONArray(find(gtCreateDateSQL.toString(), param)).getJSONObject(0).getDate("GTCREATEDATE");
        Date ltCreateDate = toJSONArray(find(ltCreateDateSQL.toString(), param)).getJSONObject(0).getDate("LTCREATEDATE");
        // 封装结果
        JSONObject result = new JSONObject();
        result.put("total",total);
        result.put("gtCreateDate",gtCreateDate);
        result.put("ltCreateDate",ltCreateDate);
        return result;
    }

    /**
     * 查询商品清单
     * @param param
     * @return
     */
    public JSONArray queryProductList(JSONObject param){
        String sSQL = "select a.* ,(select '['||B.ID||']'||b.NAME from MAS_PRODUCT b where b.id = a.PRODUCT_ID)PRODUCT,C.SALE_QTY-C.QTY_END QTY,c.FLOOR_PRICE,C.REMARKS " +
                " from PRICE_APPROVAL_REPORT A,SER_SERVICE_PLAN_ITEM C " +
                " where PLAN_ID = :ID " +
                " AND C.ID =A.PLAN_ITEM_ID" +
                " AND APPROVAL_NUM = :APPROVAL_NUM" +
                " AND A.DISCOUNT_RATE <1  ";
        return toJSONArray(find(sSQL, param));
    }
    /**
     * 查询历史三十天最大和最小优惠单价
     * @param param
     * @return
     */
    public JSONArray queryHistoryPrice(JSONObject param){
        String sSQL = "select MIN(PFT_PRICE_UNIT) min_price,max(PFT_PRICE_UNIT) max_price " +
                " from SER_SERVICE_PLAN A ,SER_SERVICE_PLAN_ITEM b ,mas_dept c " +
                " where a.id = b.SERVICE_PLAN_ID " +
                " and a.BELONG_DEPT_ID = c.id " +
                " and c.COMPANY_ID = :COMPANY_ID " +
                " AND B.PRODUCT_ID = :PRODUCT_ID" +
                " AND A.RECEIVABLE_DATE is not null " +
                " and A.CREATE_DATE > trunc(sysdate)-30 ";
        return toJSONArray(find(sSQL, param));
    }
    /**
     * 查询有效价格文件
     * @param param
     * @return
     */
    public JSONArray queryValidT03(JSONObject param){
        String sSQL = "SELECT TYPE, TO_CHAR(TRUNC(START_DATE),'YYYY-MM-DD') START_DATE,P001 " +
                " from V_VAILD_T03 " +
                " where PRODUCT_ID =:PRODUCT_ID " +
                " AND END_DATE >SYSDATE AND START_DATE<SYSDATE  " +
                " ORDER BY START_DATE DESC  ";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 根据部门查找到该部门最近内协的基地
     * @param param
     * @return
     */
    public JSONArray queryRecentlyInner(JSONObject param){
        String sSQL = " select a.BELONG_DEPT_ID " +
                "   from SER_SERVICE_PLAN a ,ser_service_plan_item b,ser_service_plan_item c,ser_service_Plan d " +
                "   where a.id = b.SERVICE_PLAN_ID and c.id = b.PARENT_ITEM_ID and c.SERVICE_PLAN_ID = d.ID " +
                "     and d.BELONG_DEPT_ID = :deptId " +
                "     and d.CREATE_DATE >sysdate-30 " +
                "   order by a.CREATE_DATE desc";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询当前车辆和门店的未结算方案
     * @param param
     * @return
     */
    public JSONArray queryServerDetail(JSONObject param){
        String sSQL = "select ' ' OPEN,(case when GET_CAR_INFO ='-'||'-' then '绑定接车单' when GET_CAR_INFO is null then '绑定接车单' else subStr(GET_CAR_INFO, instr(A1.GET_CAR_INFO,'-') + 2) end)GET_CAR_TIME,A1.ID ," +
                "A1.STATE PLAN_STATE,GET_SERVICEPLAN_STATE(A1.STATE) PLAN_STATE_NAME,A1.SALES_AMOUNT,TO_CHAR(a1.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS')CREATE_DATE,A.CAR_LICENSE " +
                " from FN_CUSTOMER_EXPAND A " +
                " left join SER_SERVICE_PLAN A1 on a1.FN_CUSTOMER_EXPAND_ID = a.id " +
                " where a.CAR_LICENSE = :CAR_LICENSE " +
                " AND A1.BELONG_DEPT_ID = :DEPT_ID" +
                " and A1.STATE not in ('receivables','termination')" +
                " order by a1.CREATE_DATE desc";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 查询当前方案接车单的服务记录
     * @param param
     * @return
     */
    public JSONArray queryServerDetailByPickCar(JSONObject param){
        String sSQL = "select ' '                                                                  OPEN, " +
                "       TO_CHAR(C.CREATE_DATE, " +
                "               'YYYY-MM-DD HH24:MI:SS') GET_CAR_TIME, " +
                "       A1.ID, " +
                "       A1.STATE " +
                "                                                                            PLAN_STATE, " +
                "       GET_SERVICEPLAN_STATE(A1.STATE)                                      PLAN_STATE_NAME, " +
                "       A1.SALES_AMOUNT, " +
                "       TO_CHAR(a1.CREATE_DATE, " +
                "               'YYYY-MM-DD HH24:MI:SS')                                     CREATE_DATE, " +
                "       A.CAR_LICENSE " +
                "from FN_PICK_UP_CAR C  " +
                "    left join SER_SERVICE_PLAN A1 on a1.PICK_CAR_ID = C.id " +
                "    left join FN_CUSTOMER_EXPAND A on a1.FN_CUSTOMER_EXPAND_ID = a.id " +
                "where C.LICENSE_NUMBER = :CAR_LICENSE " +
                "and c.DEPT_ID = :DEPT_ID " +
                "and c.ID = :ID" +
                " order by a1.CREATE_DATE desc";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 根据单号查询服务明细
     * @param param
     * @return
     */
    public JSONArray queryServerDetailItems(JSONObject param){
        String sSQL = "select '['||a.PRODUCT_ID||']'||b.NAME PRODUCT," +
                "   A.SALE_QTY-A.QTY_END QTY," +
                "   A.PRODUCT_ID PRODUCT_ID," +
                "   b.NAME PRODUCT_NAME," +
                "   A.PFT_PRICE_UNIT," +
                "   A.PRIMARY_PRICE_UNIT_SHOW," +
                "   a.SALES_AMOUNT,price_unit - pft_price_unit DISCOUNT_AMOUNT,A.DISCOUNT_RATE *100 ||'%' DISCOUNT_RATE,ROWNUM, " +
                "       (select s.NAME from HR_EMPLOYEE s where s.ORIGIN = MAIN_SALER_ID)MAIN_SALER_NAME, " +
                "       (select s.NAME from HR_EMPLOYEE s where s.ORIGIN = SUP_SALER_ID)SUP_SALER_ID,A.REMARKS " +
                "from SER_SERVICE_PLAN_ITEM A,mas_product b " +
                "where a.PRODUCT_ID = b.ID " +
                "and A.SERVICE_PLAN_ID = :ID";
        return toJSONArray(find(sSQL, param));
    }

    /**
     * 根据单号查询未计算提成明细
     * @param param
     * @return
     */
    public JSONArray queryServerEstimatedCommission(JSONObject param){
        String sSQL ="select DISTINCT  B.ID " +
                "from SER_SERVICE_PLAN_ITEM A ,SER_SERVICE_PLAN b " +
                "where ESTIMATED_COMMISSION_FLAG = '0' " +
                "  and b.id = a.SERVICE_PLAN_ID " +
                "and a.CREATE_DATE >to_date('2023-10-23 10:00:00','yyyy-MM-dd HH24:mi:ss') " +
                "and b.SUBMISSION_DATE is not null";
        return toJSONArray(find(sSQL, param));
    }

}
